package utils;

import java.util.ArrayList;  
import java.sql.*;

/**
 * This calss handles the DB connection and queries to the database.
 * This class will be used by ServerPeer.java
 */
public class MysqlConnect {
    private String host;
    private String port;
    private String user;
    private String pass;

    //The DB connection
    private Connection conn;

    public static void main(String[] args) {
	/*	
	System.out.println("MySQL Connect Example.");
	MysqlConnect mc = new MysqlConnect();

	ArrayList data = mc.fetchPeersByFile("foobar.txt");
	System.out.println(data);

	mc.insertInPeerMaster("1.3.3.1","Stars",1);
	ArrayList data1 = mc.fetchPeerMaster("","");
	System.out.println(data1);

	String[] files;
	files = new String[3];
	files[0] = "ek.txt";
	files[1] = "do.txt";
	files[2] = "teen.txt";

	mc.insertInPeerFiles("1.3.3.1","Stars",files);

	ArrayList data2 = mc.fetchPeerFiles("1.3.3.1","Stars");
	System.out.println(data2);

	mc.closeConn();
	*/
    }

    /**
     * Constructor
     */
    public MysqlConnect() {
	//System.out.println("MysqlConnect constructor.");

	this.host = "ada.cs.iit.edu";
	this.port = "3306";
	this.user = "kgomes";
	this.pass = "XiSYRsWepEuao";

	String url = "jdbc:mysql://"+this.host+":"+this.port+"/";
	String dbName = "kgomes_csrocks";
	String driver = "com.mysql.jdbc.Driver";

	try {
	    Class.forName(driver).newInstance();
	    this.conn = DriverManager.getConnection(url+dbName, this.user, this.pass);
	    System.out.println("Connected to the database");
	} catch (Exception e) {
	    e.printStackTrace();
	}
    }

    /**
     * Returns the active DB connection
     */
    public Connection getConn() {
	return this.conn;
    }

    /**
     * Closes the currently active DB connection
     */
    public void closeConn() {
	try {
	    this.conn.close();
	    System.out.println("Disconnected from database");
	} catch(Exception e) {
	    e.printStackTrace();
	}
    }

    /**
     * Fetches data from peer_master table.
     *
     * @parameters
     * peer_ip - {String} Send blank if all the peers are to be fetched.
     */
    public ArrayList fetchPeerMaster(String peer_ip, String peer_name) {
	ArrayList data = new ArrayList();
	Integer count = 0;

	String query = "SELECT * FROM peer_master";
	if(peer_ip != "" && peer_name != "") {
	    query += " WHERE ip = '"+peer_ip+"' AND name = '"+peer_name+"'";
	}

	ResultSet result = null;
	result = this.executeQuery(query);

	try {
	    while(result.next()) {
		/*
		  System.out.println(result.getRow());
		  System.out.println("Peer IP: "+result.getString("ip"));
		  System.out.println("Peer name: "+result.getString("name"));
		*/

		data.add(new ArrayList());
		((ArrayList)data.get(count)).add(result.getString("ip"));
		((ArrayList)data.get(count)).add(result.getString("name"));

		count++;
	    }
	} catch(SQLException e) {
	    e.printStackTrace();
	}

	return data;
    }

    /**
     * Fetchs the files available with a specific peer.
     * 
     * @parameters
     * peer_ip - {String} IP address of a peer.
     */
    public ArrayList fetchPeerFiles(String peer_ip, String peer_name) {
	ArrayList data = new ArrayList();
	Integer count = 0;

	String query = "SELECT * FROM peer_files pf INNER JOIN peer_master pm ON pf.peer_ip = pm.ip AND pf.peer_name = pm.name AND pf.peer_ip = '"+peer_ip+"' AND pf.peer_name = '"+peer_name+"' AND pm.status = 1";
	ResultSet result = this.executeQuery(query);

	try {
	    while(result.next()) {
		//System.out.println(result.getRow());
		//System.out.println("Filename: "+result.getString("filename"));
		//System.out.println("Peer Name: "+result.getString("peer_name"));

		data.add(new ArrayList());
		((ArrayList)data.get(count)).add(result.getString("peer_ip"));
		((ArrayList)data.get(count)).add(result.getString("peer_name"));
		((ArrayList)data.get(count)).add(result.getString("filename"));

		count++;
	    }
	} catch(SQLException e) {
	    e.printStackTrace();
	}

	return data;
    }

    /**
     * Given a filename, returns all the peers holding that file
     *
     * @parameters
     * filename - {String} Name of the file to be searched
     */
    public ArrayList fetchPeersByFile(String filename) {
	ArrayList data = new ArrayList();
	Integer count = 0;

	String query = "SELECT * FROM peer_files pf INNER JOIN peer_master pm ON pf.peer_ip = pm.ip AND pf.peer_name = pm.name AND pf.filename = '"+filename+"' AND pm.status = 1";
	ResultSet result = this.executeQuery(query);
	
	try {	    
	    while(result.next()) {
		/*
		System.out.println("Peer having "+filename+": "+result.getString("peer_ip"));
		System.out.println("Peer Name: "+result.getString("peer_name"));
		*/

		data.add(new ArrayList());
		((ArrayList)data.get(count)).add(result.getString("peer_ip"));
		((ArrayList)data.get(count)).add(result.getString("peer_name"));
		((ArrayList)data.get(count)).add(result.getString("filename"));

		count++;
	    }
	} catch(SQLException e) {
	    e.printStackTrace();
	}

	return data;
    }

    /**
     * Searches the database to find out whether the peer has the specific file.
     */
    public Boolean checkPeerFile(String peer_ip, String filename) {
	Boolean flag = false;

	String query = "SELECT * FROM peer_files WHERE peer_ip = '"+peer_ip+"' AND filename = '"+filename+"'";
	ResultSet result = this.executeQuery(query);

	try {
	    while(result.next()) {
		flag = true;
		break;
	    }
	} catch(SQLException e) {
	    e.printStackTrace();
	}

	return flag;
    }

    /**
     * Inserts/Updates records in *peer_master* table.
     * 
     * @parameters
     * peer_ip - {String} The Peer IP address
     * name - {String} Peer Name. Human readable identifier.
     * status - {Integer} Whether the peer is active or inactive. 1 or 0.
     */
    public void insertInPeerMaster(String peer_ip, String name, Integer status) {
	try {
	    //Search for a unique ip,name pair.
	    String Squery = "SELECT srno FROM peer_master WHERE name = '"+name+"' AND ip = '"+peer_ip+"'";
	    ResultSet Sresult = this.executeQuery(Squery);

	    if(Sresult.next()) {
		//If exists then update.
		String Uquery = "UPDATE peer_master SET status = "+status+" WHERE name = '"+name+"' AND ip = '"+peer_ip+"'";
		this.executeQuery(Uquery);
	    } else {
		String Iquery = "INSERT INTO peer_master(ip,name,status) values('"+peer_ip+"','"+name+"',"+status+")";
		this.executeQuery(Iquery);
	    }
	} catch(Exception e) {
	    e.printStackTrace();
	}
    }

    /**
     * Deletes records for a peer_ip<->peer_name pair and inserts new records.
     * The following two cases are handled in *Delete, then Insert*.
     * 1. Records in the XML sent by the client will be inserted in DB
     * 2. Records in the DB previously sent by Client, and (because they're deleted on Client machine)
     *     now are present in the XML.
     *
     * @parameters
     * peer_ip - {String}
     * peer_name - {String}
     * filenames - {Array} of file names
     */
    public void insertInPeerFiles(String peer_ip, String peer_name, ArrayList filenames) {
	String Dquery = "DELETE FROM peer_files WHERE peer_ip = '"+peer_ip+"' AND peer_name = '"+peer_name+"'";
	ResultSet Dresult = this.executeQuery(Dquery);

	for(int i = 0;i < filenames.size();i++) {
	    String Iquery = "INSERT INTO peer_files(peer_ip,peer_name,filename) VALUES('"+peer_ip+"','"+peer_name+"','"+filenames.get(i)+"')";
	    this.executeQuery(Iquery);
	}
    }

    /**
     * Executes a Database Query.
     */
    private ResultSet executeQuery(String query) {
	Statement stmt = null;
	ResultSet result = null;

	try {
	    stmt = this.conn.createStatement();
	    stmt.execute(query);
	    result = stmt.getResultSet();
	} catch(SQLException e) {
	    e.printStackTrace();
	}

	return result;
    }
}